package com.ruoyi.wb.utils;


import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONUtil;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.config.XlsConfig;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.wb.domain.WbEntAuth;
import com.ruoyi.wb.domain.WbPlan;
import com.ruoyi.wb.domain.WbPlanContent;
import com.ruoyi.wb.enmus.PlanTypeEnums;
import com.ruoyi.wb.vo.*;
import com.spire.xls.Worksheet;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.Arrays;
import java.util.List;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class XlsCopyToPngUtil {

    public static String copyXlsMonth(List<WbPlanClassifyVo> list, WbPlan wbPlan, boolean ifPicture) throws IOException {
        if (CollectionUtils.isEmpty(list) || ObjectUtils.isEmpty(wbPlan)) {
            return "";
        }
        InputStream inp = getInputStream();


        Workbook workbook = new XSSFWorkbook(inp);

        CellStyle style = workbook.createCellStyle();

        //边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //字体
        Font font = workbook.createFont();
        font.setFontName("宋体"); // 设置字体
        font.setFontHeightInPoints((short) 10); // 设置字号
        style.setFont(font);
        style.setWrapText(true);//自动换行
        style.setAlignment(HorizontalAlignment.CENTER);  // 设置水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        String uuid = UUID.randomUUID().toString().replaceAll("-", "");
        String outFileName = uuid + ".xlsx";


        CellStyle styleL = workbook.createCellStyle();

        //边框
        styleL.setBorderBottom(BorderStyle.THIN);
        styleL.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styleL.setBorderRight(BorderStyle.THIN);
        styleL.setRightBorderColor(IndexedColors.BLACK.getIndex());
        styleL.setBorderTop(BorderStyle.THIN);
        styleL.setTopBorderColor(IndexedColors.BLACK.getIndex());
        styleL.setBorderLeft(BorderStyle.THIN);
        styleL.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        styleL.setFont(font);
        styleL.setWrapText(true);//自动换行
        styleL.setAlignment(HorizontalAlignment.CENTER);  // 设置水平居中
        styleL.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        Font fontL = workbook.createFont();
        fontL.setFontName("宋体"); // 设置字体
        fontL.setFontHeightInPoints((short) 10); // 设置现竖向显示

        File folder = new File(XlsConfig.XLS_COPY_PATH_MONTH);
        if (!folder.exists()) {
            boolean mkdir = folder.mkdir();
            if (!mkdir) {
                throw new RuntimeException("创建文件副本目录失败");
            }
        }
        // 创建目标文件
        FileOutputStream out = new FileOutputStream(XlsConfig.XLS_COPY_PATH_MONTH + outFileName);


        //获取第0个工作簿 修改内容
        Sheet sheet = workbook.getSheetAt(0);
        //起始行
        int startRowNum = sheet.getLastRowNum() + 1;
        //最终列
        int lastCelNum = sheet.getRow(0).getLastCellNum();
        for (int i = 0; i < startRowNum; i++) {
            Row row = sheet.getRow(i);
            if (ObjectUtils.isEmpty(row)) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();//当前行列数
            for (int j = 0; j < lastCellNum; j++) {
                if (i == startRowNum - 2 && j == lastCelNum - 6) {
                    Cell cell = row.getCell(j);
                    cell.setCellValue("检查日期：" + DateUtils.getDate());
                }
                if (i == startRowNum - 2 && j == lastCelNum - 11) {
                    String clientAddress = wbPlan.getClientAddress();
                    int rowHigh = (StringUtils.isEmpty(clientAddress) ? 1 : clientAddress.length() / 20 + 1) * 15 * 20;
                    row.setHeight((short) rowHigh);
                    Cell cell = row.getCell(j);
                    cell.setCellValue("项目地址：" + (StringUtils.isEmpty(clientAddress) ? "" : clientAddress));
                }
                if (i == startRowNum - 3 && j == lastCelNum - 6) {
                    Cell cell = row.getCell(j);
                    cell.setCellValue("记录编号：" + (ObjectUtils.isEmpty(wbPlan.getContractName()) ? "" : wbPlan.getContractName()));
                }

                if (i == startRowNum - 3 && j == lastCelNum - 11) {
                    int rowHigh = (StringUtils.isEmpty(wbPlan.getPlanName()) ? 1 : wbPlan.getPlanName().length() / 20 + 1) * 15 * 20;
                    row.setHeight((short) rowHigh);
                    Cell cell = row.getCell(j);
                    cell.setCellValue("项目名称：" + (StringUtils.isEmpty(wbPlan.getPlanName()) ? "" : wbPlan.getPlanName()));
                }

                if (i == startRowNum - 8 && j == lastCelNum - 7) {
                    Cell cell = row.getCell(j);
                    cell.setCellValue(DateUtils.parseDateToStr("yyyy-MM-dd", wbPlan.getPlanStartDate()));
                }
                if (i == startRowNum - 9 && j == lastCelNum - 7) {
                    Cell cell = row.getCell(j);
                    cell.setCellValue(wbPlan.getWbCompanyName());
                }
                if (i == startRowNum - 10 && j == lastCelNum - 7) {
                    Cell cell = row.getCell(j);
                    cell.setCellValue(wbPlan.getClientName());
                }
                if (i == startRowNum - 11 && j == lastCelNum - 7) {
                    Cell cell = row.getCell(j);
                    cell.setCellValue(wbPlan.getContractName());
                }
            }
        }
        int rowNum = startRowNum;
        for (WbPlanClassifyVo wbPlanClassifyVo : list) {
            List<WbPlanContent> contentList = wbPlanClassifyVo.getContentList();

            for (int i = 0; i < contentList.size(); rowNum++, i++) {
                //创建行
                Row row = sheet.createRow(rowNum);
                row.setHeight((short) (48 * 20));

                for (int j = 0; j < lastCelNum; j++) {
                    //创建列
                    Cell cell = row.createCell(j);
                    // 设置单元格的样式和边框
                    cell.setCellStyle(style);
                    switch (j) {
                        case 0:
                            if (!ObjectUtils.isEmpty(wbPlanClassifyVo.getClassifyName())) {
                                cell.setCellStyle(style);
                                cell.setCellValue(wbPlanClassifyVo.getClassifyName());
                            }
                            break;
                        case 1:
                            if (!ObjectUtils.isEmpty(contentList.get(i).getName())) {
                                cell.setCellValue(contentList.get(i).getName());
                            }
                            break;
                        case 3:
                            if (!ObjectUtils.isEmpty(contentList.get(i).getIntro())) {
                                cell.setCellValue(contentList.get(i).getIntro());
                            }
                            break;
                        case 6:
                            cell.setCellValue(StringUtils.isEmpty(contentList.get(i).getCheckPoint()) ? PlanTypeEnums.WHIPPTREE.getDescribe() : contentList.get(i).getCheckPoint());
                            break;
                        case 7:
                            cell.setCellValue(StringUtils.isEmpty(contentList.get(i).getwbexplain()) ? PlanTypeEnums.WHIPPTREE.getDescribe() : contentList.get(i).getwbexplain());
                            break;
                        case 10:
                            String result = ObjectUtils.isEmpty(contentList.get(i).getResult())||contentList.get(i).getResult().equals(3L) ? "" : contentList.get(i).getResult().toString();
                            cell.setCellValue(StringUtils.isEmpty(result)? PlanTypeEnums.WHIPPTREE.getDescribe() : result.equals(PlanTypeEnums.RIGHT.getType()) ? PlanTypeEnums.RIGHT.getDescribe() : PlanTypeEnums.WRONG.getDescribe());
                            break;
                    }
                }
                //合并列
                sheet.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        1,
                        2
                ));

                sheet.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        3,
                        5
                ));

                sheet.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        7,
                        9
                ));

            }
            //合并行
            if (contentList.size() > 1) {
                sheet.addMergedRegion(new CellRangeAddress(
                        rowNum - contentList.size(),
                        rowNum - 1,
                        0,
                        0
                ));
            }
        }


        int k = 0;
        for (int i = 0; i < XlsConfig.BOTTOM_ROW_NUM_MONTH; i++, rowNum++, k++) {
            //创建行
            Row row = sheet.createRow(rowNum);
            row.setHeight((short) (14 * 20));
            //创建列
            for (int j = 0; j < lastCelNum; j++) {
                //创建列
                Cell cell = row.createCell(j);
                CellStyle cellStyle = workbook.createCellStyle();
                //边框
                cellStyle.setBorderBottom(BorderStyle.THIN);
                cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                cellStyle.setBorderRight(BorderStyle.THIN);
                cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                cellStyle.setBorderTop(BorderStyle.THIN);
                cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                cellStyle.setBorderLeft(BorderStyle.THIN);
                cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                //字体
                Font font1 = workbook.createFont();
                font1.setFontName("宋体"); // 设置字体
                font1.setFontHeightInPoints((short) 10); // 设置字号
                cellStyle.setFont(font);
                cellStyle.setWrapText(true);//自动换行
                cellStyle.setAlignment(HorizontalAlignment.CENTER);  // 设置水平居中
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                // 设置单元格的样式和边框
                cell.setCellStyle(style);
                switch (k) {
                    case 0:
                        switch (j) {
                            case 0:
                                cell.setCellValue("其他");
                                break;
                            case 1:
                                int rowHigh = ((StringUtils.isEmpty(wbPlan.getRests()) ? 1 : wbPlan.getRests().length()) / 20 + 1) * 15 * 20;
                                row.setHeight((short) rowHigh);
                                cell.setCellValue(wbPlan.getRests());
                                break;
                        }
                        break;
                    case 1:
                        switch (j) {
                            case 0:
                                cell.setCellValue("培训");
                                break;
                            case 1:
                                int rowHigh = ((StringUtils.isEmpty(wbPlan.getTrain()) ? 1 : wbPlan.getTrain().length()) / 20 + 1) * 15 * 20;
                                row.setHeight((short) rowHigh);
                                cell.setCellValue(wbPlan.getTrain());
                                break;
                        }
                        break;
                    case 2:
                        switch (j) {
                            case 0:
                                cell.setCellValue("问题汇总");
                                break;
                            case 1:
                                int rowHigh = ((StringUtils.isEmpty(wbPlan.getCollect()) ? 1 : wbPlan.getCollect().length()) / 20 + 1) * 15 * 20;
                                row.setHeight((short) rowHigh);
                                cell.setCellValue(wbPlan.getCollect());
                                break;
                        }
                        break;
                    case 5:
                        switch (j) {
                            case 0:
                                cell.setCellValue("更换材料清单");
                                break;
                            case 1:
                                int rowHigh = ((StringUtils.isEmpty(wbPlan.getInventory()) ? 1 : wbPlan.getInventory().length()) / 20 + 1) * 15 * 20;
                                row.setHeight((short) rowHigh);
                                cell.setCellValue(wbPlan.getInventory());
                                break;
                        }
                        break;
                    case 8:
                        switch (j) {
                            case 0:
                                cell.setCellValue("建议方案");
                                break;
                            case 1:
                                int rowHigh = ((StringUtils.isEmpty(wbPlan.getScheme()) ? 1 : wbPlan.getScheme().length()) / 20 + 1) * 15 * 20;
                                row.setHeight((short) rowHigh);
                                cell.setCellValue(wbPlan.getScheme());
                                break;
                        }
                        break;
                    case 11:
                        row.setHeight((short) (28 * 20));
                        switch (j) {
                            case 0:
                                cell.setCellValue("甲方");
                                break;
                            case 1:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("现场人员(签字)：");
                                break;
                            case 5:
                                cell.setCellValue("乙方");
                                break;
                            case 6:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("维保人员(签字)：");
                                break;
                        }
                        break;
                    case 12:
                        row.setHeight((short) (28 * 20));
                        switch (j) {
                            case 1:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("项目主管(签字)：");
                                break;
                            case 6:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("项目主管(签字)：");
                                break;
                        }
                        break;
                    case 13:
                        row.setHeight((short) (28 * 20));
                        switch (j) {
                            case 1:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("委托单位(签章)：");
                                break;
                            case 6:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("技术负责人(签章)：");
                                break;
                        }
                        break;
                    case 14:
                        row.setHeight((short) (42 * 32));
                        switch (j) {
                            case 6:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("维保单位(签章)：");
                                break;
                        }
                        break;
                    case 15:
                        switch (j) {
                            case 0:
                                cell.setCellValue("备注:□后含附件(□现场检查凭条，□现场检查图片，□问题反馈函，□维修方案及报价；□其他)□以下空白");
                                break;
                        }
                        break;
                    case 16:
                        switch (j) {
                            case 0:
                                cell.setCellValue("以上问题，请即时整改，否则一切损失，我方概不负责");
                                break;
                        }
                        break;
                }
            }
        }
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 1,
                rowNum - 1,
                0,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 2,
                rowNum - 2,
                0,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 6,
                rowNum - 3,
                0,
                0
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 4,
                rowNum - 3,
                1,
                3
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 4,
                rowNum - 3,
                4,
                4
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 5,
                rowNum - 5,
                1,
                3
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 6,
                rowNum - 6,
                1,
                3
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 3,
                rowNum - 3,
                6,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 4,
                rowNum - 4,
                6,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 5,
                rowNum - 5,
                6,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 6,
                rowNum - 6,
                6,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 6,
                rowNum - 3,
                5,
                5
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 9,
                rowNum - 7,
                0,
                0
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 9,
                rowNum - 7,
                1,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 12,
                rowNum - 10,
                0,
                0
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 12,
                rowNum - 10,
                1,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 15,
                rowNum - 13,
                0,
                0
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 15,
                rowNum - 13,
                1,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 16,
                rowNum - 16,
                1,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 17,
                rowNum - 17,
                1,
                10
        ));

        //公司logo
        String commonLogo = wbPlan.getCommonLogo();

        //公司公章
        String commonSeal = wbPlan.getCommonSeal();

        //操作员签名
        String operatorSignature = wbPlan.getOperatorSignature();

        //工程师1 签名
        String engineerSignature = wbPlan.getEngineerSignature();

        //工程师1 印章
        String engineerPersonalSeal = wbPlan.getEngineerPersonalSeal();

        //工程师2 签名
        String firemenSignature = wbPlan.getFiremenSignature();

        //工程师2 印章
        String firemenPersonalSeal = wbPlan.getFiremenPersonalSeal();

        //设置图片绘制的位置，以及范围
        // 创建一个绘图对象
        CreationHelper helper = workbook.getCreationHelper();
        Drawing<?> drawing = sheet.createDrawingPatriarch();


        if (!StringUtils.isEmpty(commonLogo)) {
            File engineerSig = new File(commonLogo.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
            if (engineerSig.exists()) {
                //-----------维保公司logo
                byte[] operatorSigBytes = FileUtils.readFileToByteArray(engineerSig);
                ClientAnchor operatorSigAnchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 3, 6);

                int operatorSigPictureIdx = workbook.addPicture(operatorSigBytes, Workbook.PICTURE_TYPE_PNG);
                // 插入图片 维保公司logo
                drawing.createPicture(operatorSigAnchor, operatorSigPictureIdx);
            }
        }

        if (!StringUtils.isEmpty(commonSeal)) {
            File engineerSig = new File(commonSeal.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
            if (engineerSig.exists()) {
                //-----------维保公司印章
                byte[] operatorSigBytes = FileUtils.readFileToByteArray(engineerSig);
                ClientAnchor operatorSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, rowNum - 3, 11, rowNum - 2);

                int operatorSigPictureIdx = workbook.addPicture(operatorSigBytes, Workbook.PICTURE_TYPE_PNG);
                // 插入图片 维保公司印章
                drawing.createPicture(operatorSigAnchor, operatorSigPictureIdx);
            }
        }


        if (!StringUtils.isEmpty(operatorSignature)) {
            File engineerSig = new File(operatorSignature.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
            if (engineerSig.exists()) {
                //-----------操作员签名
                byte[] operatorSigBytes = FileUtils.readFileToByteArray(engineerSig);
                ClientAnchor operatorSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, rowNum - 6, 11, rowNum - 5);

                int operatorSigPictureIdx = workbook.addPicture(operatorSigBytes, Workbook.PICTURE_TYPE_PNG);
                // 插入图片 操作员 签名
                drawing.createPicture(operatorSigAnchor, operatorSigPictureIdx);

            }
        }


//        if (!StringUtils.isEmpty(engineerSignature)) {
//            File engineerSig = new File(engineerSignature.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
//            if (engineerSig.exists()) {
//                //-----------工程师1签名
//                byte[] engineerSigBytes = FileUtils.readFileToByteArray(engineerSig);
//                ClientAnchor engineerSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, rowNum - 4, 11, rowNum - 3);
//
//                int engineerSigPictureIdx = workbook.addPicture(engineerSigBytes, Workbook.PICTURE_TYPE_PNG);
//                // 插入图片 工程师1 签名
//                drawing.createPicture(engineerSigAnchor, engineerSigPictureIdx);
//
//            }
//        }

        if (!StringUtils.isEmpty(engineerPersonalSeal)) {
            File engineerSig = new File(engineerPersonalSeal.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
            if (engineerSig.exists()) {
                //-----------工程师1签名印章
                byte[] engineerSigBytes = FileUtils.readFileToByteArray(engineerSig);
                ClientAnchor engineerSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, rowNum - 4, 11, rowNum - 3);

                int engineerSigPictureIdx = workbook.addPicture(engineerSigBytes, Workbook.PICTURE_TYPE_PNG);
                // 插入图片 工程师1 签名印章
                drawing.createPicture(engineerSigAnchor, engineerSigPictureIdx);

            }
        }


//        if (!StringUtils.isEmpty(firemenSignature)) {
//            File firemenSig = new File(firemenSignature.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
//            if (firemenSig.exists()) {
//                //-----------工程师2签名
//                byte[] firemenSigBytes = FileUtils.readFileToByteArray(firemenSig);
//                // 创建一个图片对象
//                ClientAnchor firemenSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, rowNum - 5, 11, rowNum - 4);
//                int firemenSigPictureIdx = workbook.addPicture(firemenSigBytes, Workbook.PICTURE_TYPE_PNG);
//                // 插入图片 工程师2 签名
//                drawing.createPicture(firemenSigAnchor, firemenSigPictureIdx);
//            }
//        }

        if (!StringUtils.isEmpty(firemenPersonalSeal)) {
            File firemenSig = new File(firemenPersonalSeal.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
            if (firemenSig.exists()) {
                //-----------工程师2签名印章
                byte[] firemenSigBytes = FileUtils.readFileToByteArray(firemenSig);
                // 创建一个图片对象
                ClientAnchor firemenSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, rowNum - 5, 11, rowNum - 4);
                int firemenSigPictureIdx = workbook.addPicture(firemenSigBytes, Workbook.PICTURE_TYPE_PNG);
                // 插入图片 工程师2 签名印章
                drawing.createPicture(firemenSigAnchor, firemenSigPictureIdx);
            }
        }

        //是否带图片
        if (ifPicture) {
            CellStyle pngStyle = workbook.createCellStyle();
            pngStyle.setAlignment(HorizontalAlignment.CENTER);  // 设置水平居中
            pngStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            pngStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            pngStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
            boolean entrySlipRowAddFlag = false;

            //拿到图片
            if (StringUtils.isNotEmpty(wbPlan.getEntrySlip())) {
                List<String> entrySlip = Arrays.asList(wbPlan.getEntrySlip().split(","));
                //凭条
                Row pngRow = sheet.createRow(rowNum);
                Cell pngCell = pngRow.createCell(0);
                pngCell.setCellStyle(pngStyle);
                pngCell.setCellValue("现场检查凭条");
                sheet.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        0,
                        10
                ));
                rowNum += 1;
                for (int i = 0; i < entrySlip.size(); i++) {
                    File file = new File(entrySlip.get(i).replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                    if (!file.exists()) {
                        if (i == entrySlip.size() - 1) {
                            rowNum += 16;
                        }
                        continue;
                    }
                    byte[] bytes = FileUtils.readFileToByteArray(file);
                    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                    Row row = null;
                    if (entrySlipRowAddFlag) {
                        row = sheet.getRow(rowNum);
                    } else {
                        row = sheet.createRow(rowNum);

                    }

                    Cell cell = null;
                    if (entrySlipRowAddFlag) {
                        cell = row.createCell(6);
                    } else {
                        cell = row.createCell(0);
                    }
                    // 创建一个图片对象
                    ClientAnchor anchor = helper.createClientAnchor();
                    anchor.setCol1(cell.getColumnIndex());
                    anchor.setRow1(cell.getRowIndex());
                    if (entrySlipRowAddFlag) {
                        anchor.setCol2(cell.getColumnIndex() + 11 - 6);
                    } else {
                        anchor.setCol2(cell.getColumnIndex() + 5);
                    }

                    anchor.setRow2(cell.getRowIndex() + 15);
                    // 插入图片
                    drawing.createPicture(anchor, pictureIdx);
                    if (entrySlipRowAddFlag || i == entrySlip.size() - 1) {
                        rowNum += 16;
                    }
                    entrySlipRowAddFlag = !entrySlipRowAddFlag;

                }
            }

            if (StringUtils.isNotEmpty(wbPlan.getSitePhoto())) {
                List<String> sitePhoto = Arrays.asList(wbPlan.getSitePhoto().split(","));
                boolean sitePhotoRowAddFlag = false;
                //图片
                Row pngRow = sheet.createRow(rowNum);
                Cell pngCell = pngRow.createCell(0);
                pngCell.setCellStyle(pngStyle);
                pngCell.setCellValue("现场检查照片");
                sheet.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        0,
                        10
                ));
                rowNum += 1;
                for (int i = 0; i < sitePhoto.size(); i++) {
                    File file = new File(sitePhoto.get(i).replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                    if (!file.exists()) {
                        continue;
                    }
                    byte[] bytes = FileUtils.readFileToByteArray(file);
                    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                    Row row = null;
                    if (sitePhotoRowAddFlag) {
                        row = sheet.getRow(rowNum);
                    } else {
                        row = sheet.createRow(rowNum);

                    }
                    Cell cell = null;
                    if (sitePhotoRowAddFlag) {
                        cell = row.createCell(6);
                    } else {
                        cell = row.createCell(0);
                    }
                    // 创建一个图片对象
                    ClientAnchor anchor = helper.createClientAnchor();
                    anchor.setCol1(cell.getColumnIndex());
                    anchor.setRow1(cell.getRowIndex());
                    if (sitePhotoRowAddFlag) {
                        anchor.setCol2(cell.getColumnIndex() + 11 - 6);
                    } else {
                        anchor.setCol2(cell.getColumnIndex() + 5);
                    }
                    anchor.setRow2(cell.getRowIndex() + 15);
                    // 插入图片
                    drawing.createPicture(anchor, pictureIdx);
                    if (sitePhotoRowAddFlag || i == sitePhoto.size() - 1) {
                        rowNum += 16;
                    }
                    sitePhotoRowAddFlag = !sitePhotoRowAddFlag;
                }
            }
        }


        // 将源文件的内容复制到目标文件
        workbook.write(out);
        // 关闭流
        out.close();
        inp.close();
        workbook.close();
        copyXlsMonth2( list,  wbPlan,  ifPicture,uuid);
        return outFileName;
    }


    public static String copyXlsMonth2(List<WbPlanClassifyVo> list, WbPlan wbPlan, boolean ifPicture,String uuid) throws IOException {
        if (CollectionUtils.isEmpty(list) || ObjectUtils.isEmpty(wbPlan)) {
            return "";
        }
        InputStream inp = getInputStream();


        Workbook workbook = new XSSFWorkbook(inp);

        CellStyle style = workbook.createCellStyle();

        //边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //字体
        Font font = workbook.createFont();
        font.setFontName("宋体"); // 设置字体
        font.setFontHeightInPoints((short) 10); // 设置字号
        style.setFont(font);
        style.setWrapText(true);//自动换行
        style.setAlignment(HorizontalAlignment.CENTER);  // 设置水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        String outFileName = uuid + "split.xlsx";


        CellStyle styleL = workbook.createCellStyle();

        //边框
        styleL.setBorderBottom(BorderStyle.THIN);
        styleL.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styleL.setBorderRight(BorderStyle.THIN);
        styleL.setRightBorderColor(IndexedColors.BLACK.getIndex());
        styleL.setBorderTop(BorderStyle.THIN);
        styleL.setTopBorderColor(IndexedColors.BLACK.getIndex());
        styleL.setBorderLeft(BorderStyle.THIN);
        styleL.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        styleL.setFont(font);
        styleL.setWrapText(true);//自动换行
        styleL.setAlignment(HorizontalAlignment.CENTER);  // 设置水平居中
        styleL.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        Font fontL = workbook.createFont();
        fontL.setFontName("宋体"); // 设置字体
        fontL.setFontHeightInPoints((short) 10); // 设置现竖向显示

        File folder = new File(XlsConfig.XLS_COPY_PATH_MONTH);
        if (!folder.exists()) {
            boolean mkdir = folder.mkdir();
            if (!mkdir) {
                throw new RuntimeException("创建文件副本目录失败");
            }
        }
        // 创建目标文件
        FileOutputStream out = new FileOutputStream(XlsConfig.XLS_COPY_PATH_MONTH + outFileName);


        //获取第0个工作簿 修改内容
        Sheet sheet = workbook.getSheetAt(0);
        //起始行
        int startRowNum = sheet.getLastRowNum() + 1;
        //最终列
        int lastCelNum = sheet.getRow(0).getLastCellNum();
        for (int i = 0; i < startRowNum; i++) {
            Row row = sheet.getRow(i);
            if (ObjectUtils.isEmpty(row)) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();//当前行列数
            for (int j = 0; j < lastCellNum; j++) {
                if (i == startRowNum - 2 && j == lastCelNum - 6) {
                    Cell cell = row.getCell(j);
                    cell.setCellValue("检查日期：" + DateUtils.getDate());
                }
                if (i == startRowNum - 2 && j == lastCelNum - 11) {
                    String clientAddress = wbPlan.getClientAddress();
                    int rowHigh = (StringUtils.isEmpty(clientAddress) ? 1 : clientAddress.length() / 20 + 1) * 15 * 20;
                    row.setHeight((short) rowHigh);
                    Cell cell = row.getCell(j);
                    cell.setCellValue("项目地址：" + (StringUtils.isEmpty(clientAddress) ? "" : clientAddress));
                }
                if (i == startRowNum - 3 && j == lastCelNum - 6) {
                    Cell cell = row.getCell(j);
                    cell.setCellValue("记录编号：" + (ObjectUtils.isEmpty(wbPlan.getContractName()) ? "" : wbPlan.getContractName()));
                }

                if (i == startRowNum - 3 && j == lastCelNum - 11) {
                    int rowHigh = (StringUtils.isEmpty(wbPlan.getPlanName()) ? 1 : wbPlan.getPlanName().length() / 20 + 1) * 15 * 20;
                    row.setHeight((short) rowHigh);
                    Cell cell = row.getCell(j);
                    cell.setCellValue("项目名称：" + (StringUtils.isEmpty(wbPlan.getPlanName()) ? "" : wbPlan.getPlanName()));
                }

                if (i == startRowNum - 8 && j == lastCelNum - 7) {
                    Cell cell = row.getCell(j);
                    cell.setCellValue(DateUtils.parseDateToStr("yyyy-MM-dd", wbPlan.getPlanStartDate()));
                }
                if (i == startRowNum - 9 && j == lastCelNum - 7) {
                    Cell cell = row.getCell(j);
                    cell.setCellValue(wbPlan.getWbCompanyName());
                }
                if (i == startRowNum - 10 && j == lastCelNum - 7) {
                    Cell cell = row.getCell(j);
                    cell.setCellValue(wbPlan.getClientName());
                }
                if (i == startRowNum - 11 && j == lastCelNum - 7) {
                    Cell cell = row.getCell(j);
                    cell.setCellValue(wbPlan.getContractName());
                }
            }
        }
        int rowNum = startRowNum;
        for (WbPlanClassifyVo wbPlanClassifyVo : list) {
            List<WbPlanContent> contentList = wbPlanClassifyVo.getContentList();

            for (int i = 0; i < contentList.size(); rowNum++, i++) {
                //创建行
                Row row = sheet.createRow(rowNum);
                row.setHeight((short) (48 * 20));

                for (int j = 0; j < lastCelNum; j++) {
                    //创建列
                    Cell cell = row.createCell(j);
                    // 设置单元格的样式和边框
                    cell.setCellStyle(style);
                    switch (j) {
                        case 0:
                            if (!ObjectUtils.isEmpty(wbPlanClassifyVo.getClassifyName())) {
                                cell.setCellStyle(style);
                                cell.setCellValue(wbPlanClassifyVo.getClassifyName());
                            }
                            break;
                        case 1:
                            if (!ObjectUtils.isEmpty(contentList.get(i).getName())) {
                                cell.setCellValue(contentList.get(i).getName());
                            }
                            break;
                        case 3:
                            if (!ObjectUtils.isEmpty(contentList.get(i).getIntro())) {
                                cell.setCellValue(contentList.get(i).getIntro());
                            }
                            break;
                        case 6:
                            cell.setCellValue(StringUtils.isEmpty(contentList.get(i).getCheckPoint()) ? PlanTypeEnums.WHIPPTREE.getDescribe() : contentList.get(i).getCheckPoint());
                            break;
                        case 7:
                            cell.setCellValue(StringUtils.isEmpty(contentList.get(i).getwbexplain()) ? PlanTypeEnums.WHIPPTREE.getDescribe() : contentList.get(i).getwbexplain());
                            break;
                        case 10:
                            String result = ObjectUtils.isEmpty(contentList.get(i).getResult())||contentList.get(i).getResult().equals(3L) ? "" : contentList.get(i).getResult().toString();
                            cell.setCellValue(StringUtils.isEmpty(result) ? PlanTypeEnums.WHIPPTREE.getDescribe() : result.equals(PlanTypeEnums.RIGHT.getType()) ? PlanTypeEnums.RIGHT.getDescribe() : PlanTypeEnums.WRONG.getDescribe());
                            break;
                    }
                }
                //合并列
                sheet.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        1,
                        2
                ));

                sheet.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        3,
                        5
                ));

                sheet.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        7,
                        9
                ));

            }
            //合并行
            if (contentList.size() > 1) {
                sheet.addMergedRegion(new CellRangeAddress(
                        rowNum - contentList.size(),
                        rowNum - 1,
                        0,
                        0
                ));
            }
        }


        int k = 0;
        for (int i = 0; i < XlsConfig.BOTTOM_ROW_NUM_MONTH; i++, rowNum++, k++) {
            //创建行
            Row row = sheet.createRow(rowNum);
            row.setHeight((short) (14 * 20));
            //创建列
            for (int j = 0; j < lastCelNum; j++) {
                //创建列
                Cell cell = row.createCell(j);
                CellStyle cellStyle = workbook.createCellStyle();
                //边框
                cellStyle.setBorderBottom(BorderStyle.THIN);
                cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                cellStyle.setBorderRight(BorderStyle.THIN);
                cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                cellStyle.setBorderTop(BorderStyle.THIN);
                cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                cellStyle.setBorderLeft(BorderStyle.THIN);
                cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                //字体
                Font font1 = workbook.createFont();
                font1.setFontName("宋体"); // 设置字体
                font1.setFontHeightInPoints((short) 10); // 设置字号
                cellStyle.setFont(font);
                cellStyle.setWrapText(true);//自动换行
                cellStyle.setAlignment(HorizontalAlignment.CENTER);  // 设置水平居中
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                // 设置单元格的样式和边框
                cell.setCellStyle(style);
                switch (k) {
                    case 0:
                        switch (j) {
                            case 0:
                                cell.setCellValue("其他");
                                break;
                            case 1:
                                int rowHigh = ((StringUtils.isEmpty(wbPlan.getRests()) ? 1 : wbPlan.getRests().length()) / 20 + 1) * 15 * 20;
                                row.setHeight((short) rowHigh);
                                cell.setCellValue(wbPlan.getRests());
                                break;
                        }
                        break;
                    case 1:
                        switch (j) {
                            case 0:
                                cell.setCellValue("培训");
                                break;
                            case 1:
                                int rowHigh = ((StringUtils.isEmpty(wbPlan.getTrain()) ? 1 : wbPlan.getTrain().length()) / 20 + 1) * 15 * 20;
                                row.setHeight((short) rowHigh);
                                cell.setCellValue(wbPlan.getTrain());
                                break;
                        }
                        break;
                    case 2:
                        switch (j) {
                            case 0:
                                cell.setCellValue("问题汇总");
                                break;
                            case 1:
                                int rowHigh = ((StringUtils.isEmpty(wbPlan.getCollect()) ? 1 : wbPlan.getCollect().length()) / 20 + 1) * 15 * 20;
                                row.setHeight((short) rowHigh);
                                cell.setCellValue(wbPlan.getCollect());
                                break;
                        }
                        break;
                    case 5:
                        switch (j) {
                            case 0:
                                cell.setCellValue("更换材料清单");
                                break;
                            case 1:
                                int rowHigh = ((StringUtils.isEmpty(wbPlan.getInventory()) ? 1 : wbPlan.getInventory().length()) / 20 + 1) * 15 * 20;
                                row.setHeight((short) rowHigh);
                                cell.setCellValue(wbPlan.getInventory());
                                break;
                        }
                        break;
                    case 8:
                        switch (j) {
                            case 0:
                                cell.setCellValue("建议方案");
                                break;
                            case 1:
                                int rowHigh = ((StringUtils.isEmpty(wbPlan.getScheme()) ? 1 : wbPlan.getScheme().length()) / 20 + 1) * 15 * 20;
                                row.setHeight((short) rowHigh);
                                cell.setCellValue(wbPlan.getScheme());
                                break;
                        }
                        break;
                    case 11:
                        row.setHeight((short) (28 * 20));
                        switch (j) {
                            case 0:
                                cell.setCellValue("甲方");
                                break;
                            case 1:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("现场人员(签字)：");
                                break;
                            case 5:
                                cell.setCellValue("乙方");
                                break;
                            case 6:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("维保人员(签字)：");
                                break;
                        }
                        break;
                    case 12:
                        row.setHeight((short) (28 * 20));
                        switch (j) {
                            case 1:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("项目主管(签字)：");
                                break;
                            case 6:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("项目主管(签字)：");
                                break;
                        }
                        break;
                    case 13:
                        row.setHeight((short) (28 * 20));
                        switch (j) {
                            case 1:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("委托单位(签章)：");
                                break;
                            case 6:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("技术负责人(签章)：");
                                break;
                        }
                        break;
                    case 14:
                        row.setHeight((short) (42 * 32));
                        switch (j) {
                            case 6:
                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue("维保单位(签章)：");
                                break;
                        }
                        break;
                    case 15:
                        switch (j) {
                            case 0:
                                cell.setCellValue("备注:□后含附件(□现场检查凭条，□现场检查图片，□问题反馈函，□维修方案及报价；□其他)□以下空白");
                                break;
                        }
                        break;
                    case 16:
                        switch (j) {
                            case 0:
                                cell.setCellValue("以上问题，请即时整改，否则一切损失，我方概不负责");
                                break;
                        }
                        break;
                }
            }
        }
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 1,
                rowNum - 1,
                0,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 2,
                rowNum - 2,
                0,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 6,
                rowNum - 3,
                0,
                0
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 4,
                rowNum - 3,
                1,
                3
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 4,
                rowNum - 3,
                4,
                4
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 5,
                rowNum - 5,
                1,
                3
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 6,
                rowNum - 6,
                1,
                3
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 3,
                rowNum - 3,
                6,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 4,
                rowNum - 4,
                6,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 5,
                rowNum - 5,
                6,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 6,
                rowNum - 6,
                6,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 6,
                rowNum - 3,
                5,
                5
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 9,
                rowNum - 7,
                0,
                0
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 9,
                rowNum - 7,
                1,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 12,
                rowNum - 10,
                0,
                0
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 12,
                rowNum - 10,
                1,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 15,
                rowNum - 13,
                0,
                0
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 15,
                rowNum - 13,
                1,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 16,
                rowNum - 16,
                1,
                10
        ));
        //合并行
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum - 17,
                rowNum - 17,
                1,
                10
        ));

        //公司logo
        String commonLogo = wbPlan.getCommonLogo();

        //公司公章
        String commonSeal = wbPlan.getCommonSeal();

        //操作员签名
        String operatorSignature = wbPlan.getOperatorSignature();

        //工程师1 签名
        String engineerSignature = wbPlan.getEngineerSignature();

        //工程师1 印章
        String engineerPersonalSeal = wbPlan.getEngineerPersonalSeal();

        //工程师2 签名
        String firemenSignature = wbPlan.getFiremenSignature();

        //工程师2 印章
        String firemenPersonalSeal = wbPlan.getFiremenPersonalSeal();

        //设置图片绘制的位置，以及范围
        // 创建一个绘图对象
        CreationHelper helper = workbook.getCreationHelper();
        Drawing<?> drawing = sheet.createDrawingPatriarch();


        if (!StringUtils.isEmpty(commonLogo)) {
            File engineerSig = new File(commonLogo.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
            if (engineerSig.exists()) {
                //-----------维保公司logo
                byte[] operatorSigBytes = FileUtils.readFileToByteArray(engineerSig);
                ClientAnchor operatorSigAnchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 3, 6);

                int operatorSigPictureIdx = workbook.addPicture(operatorSigBytes, Workbook.PICTURE_TYPE_PNG);
                // 插入图片 维保公司logo
                drawing.createPicture(operatorSigAnchor, operatorSigPictureIdx);
            }
        }

        if (!StringUtils.isEmpty(commonSeal)) {
            File engineerSig = new File(commonSeal.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
            if (engineerSig.exists()) {
                //-----------维保公司印章
                byte[] operatorSigBytes = FileUtils.readFileToByteArray(engineerSig);
                ClientAnchor operatorSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, rowNum - 3, 11, rowNum - 2);

                int operatorSigPictureIdx = workbook.addPicture(operatorSigBytes, Workbook.PICTURE_TYPE_PNG);
                // 插入图片 维保公司印章
                drawing.createPicture(operatorSigAnchor, operatorSigPictureIdx);
            }
        }


        if (!StringUtils.isEmpty(operatorSignature)) {
            File engineerSig = new File(operatorSignature.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
            if (engineerSig.exists()) {
                //-----------操作员签名
                byte[] operatorSigBytes = FileUtils.readFileToByteArray(engineerSig);
                ClientAnchor operatorSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, rowNum - 6, 11, rowNum - 5);

                int operatorSigPictureIdx = workbook.addPicture(operatorSigBytes, Workbook.PICTURE_TYPE_PNG);
                // 插入图片 操作员 签名
                drawing.createPicture(operatorSigAnchor, operatorSigPictureIdx);

            }
        }


//        if (!StringUtils.isEmpty(engineerSignature)) {
//            File engineerSig = new File(engineerSignature.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
//            if (engineerSig.exists()) {
//                //-----------工程师1签名
//                byte[] engineerSigBytes = FileUtils.readFileToByteArray(engineerSig);
//                ClientAnchor engineerSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, rowNum - 4, 11, rowNum - 3);
//
//                int engineerSigPictureIdx = workbook.addPicture(engineerSigBytes, Workbook.PICTURE_TYPE_PNG);
//                // 插入图片 工程师1 签名
//                drawing.createPicture(engineerSigAnchor, engineerSigPictureIdx);
//
//            }
//        }

        if (!StringUtils.isEmpty(engineerPersonalSeal)) {
            File engineerSig = new File(engineerPersonalSeal.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
            if (engineerSig.exists()) {
                //-----------工程师1签名印章
                byte[] engineerSigBytes = FileUtils.readFileToByteArray(engineerSig);
                ClientAnchor engineerSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, rowNum - 4, 11, rowNum - 3);

                int engineerSigPictureIdx = workbook.addPicture(engineerSigBytes, Workbook.PICTURE_TYPE_PNG);
                // 插入图片 工程师1 签名印章
                drawing.createPicture(engineerSigAnchor, engineerSigPictureIdx);

            }
        }


//        if (!StringUtils.isEmpty(firemenSignature)) {
//            File firemenSig = new File(firemenSignature.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
//            if (firemenSig.exists()) {
//                //-----------工程师2签名
//                byte[] firemenSigBytes = FileUtils.readFileToByteArray(firemenSig);
//                // 创建一个图片对象
//                ClientAnchor firemenSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, rowNum - 5, 11, rowNum - 4);
//                int firemenSigPictureIdx = workbook.addPicture(firemenSigBytes, Workbook.PICTURE_TYPE_PNG);
//                // 插入图片 工程师2 签名
//                drawing.createPicture(firemenSigAnchor, firemenSigPictureIdx);
//            }
//        }

        if (!StringUtils.isEmpty(firemenPersonalSeal)) {
            File firemenSig = new File(firemenPersonalSeal.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
            if (firemenSig.exists()) {
                //-----------工程师2签名印章
                byte[] firemenSigBytes = FileUtils.readFileToByteArray(firemenSig);
                // 创建一个图片对象
                ClientAnchor firemenSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, rowNum - 5, 11, rowNum - 4);
                int firemenSigPictureIdx = workbook.addPicture(firemenSigBytes, Workbook.PICTURE_TYPE_PNG);
                // 插入图片 工程师2 签名印章
                drawing.createPicture(firemenSigAnchor, firemenSigPictureIdx);
            }
        }


        //是否带图片
        if (ifPicture) {

            CellStyle pngStyle = workbook.createCellStyle();
            pngStyle.setAlignment(HorizontalAlignment.CENTER);  // 设置水平居中
            pngStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            pngStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            pngStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
            boolean entrySlipRowAddFlag = false;

            //拿到图片
            if (StringUtils.isNotEmpty(wbPlan.getEntrySlip())) {
                Sheet sheet1 = workbook.getSheetAt(1);
                Drawing<?> drawing1 = sheet1.createDrawingPatriarch();
                rowNum=0;
                List<String> entrySlip = Arrays.asList(wbPlan.getEntrySlip().split(","));
                //凭条
                Row pngRow = sheet1.createRow(rowNum);
                Cell pngCell = pngRow.createCell(0);
                pngCell.setCellStyle(pngStyle);
                pngCell.setCellValue("现场检查凭条");
                sheet1.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        0,
                        10
                ));
                rowNum += 1;
                for (int i = 0; i < entrySlip.size(); i++) {
                    File file = new File(entrySlip.get(i).replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                    if (!file.exists()) {
                        if (i == entrySlip.size() - 1) {
                            rowNum += 16;
                        }
                        continue;
                    }
                    byte[] bytes = FileUtils.readFileToByteArray(file);
                    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                    Row row = null;
                    if (entrySlipRowAddFlag) {
                        row = sheet1.getRow(rowNum);
                    } else {
                        row = sheet1.createRow(rowNum);

                    }

                    Cell cell = null;
                    if (entrySlipRowAddFlag) {
                        cell = row.createCell(6);
                    } else {
                        cell = row.createCell(0);
                    }
                    // 创建一个图片对象
                    ClientAnchor anchor = helper.createClientAnchor();
                    anchor.setCol1(cell.getColumnIndex());
                    anchor.setRow1(cell.getRowIndex());
                    if (entrySlipRowAddFlag) {
                        anchor.setCol2(cell.getColumnIndex() + 11 - 6);
                    } else {
                        anchor.setCol2(cell.getColumnIndex() + 5);
                    }

                    anchor.setRow2(cell.getRowIndex() + 15);
                    // 插入图片
                    drawing1.createPicture(anchor, pictureIdx);
                    if (entrySlipRowAddFlag || i == entrySlip.size() - 1) {
                        rowNum += 16;
                    }
                    entrySlipRowAddFlag = !entrySlipRowAddFlag;

                }
            }

            if (StringUtils.isNotEmpty(wbPlan.getSitePhoto())) {
                rowNum=0;
                Sheet sheet2 = workbook.getSheetAt(2);
                Drawing<?> drawing2 = sheet2.createDrawingPatriarch();
                List<String> sitePhoto = Arrays.asList(wbPlan.getSitePhoto().split(","));
                boolean sitePhotoRowAddFlag = false;
                //图片
                Row pngRow = sheet2.createRow(rowNum);
                Cell pngCell = pngRow.createCell(0);
                pngCell.setCellStyle(pngStyle);
                pngCell.setCellValue("现场检查照片");
                sheet2.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        0,
                        10
                ));
                rowNum += 1;
                for (int i = 0; i < sitePhoto.size(); i++) {
                    File file = new File(sitePhoto.get(i).replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                    if (!file.exists()) {
                        continue;
                    }
                    byte[] bytes = FileUtils.readFileToByteArray(file);
                    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                    Row row = null;
                    if (sitePhotoRowAddFlag) {
                        row = sheet2.getRow(rowNum);
                    } else {
                        row = sheet2.createRow(rowNum);

                    }
                    Cell cell = null;
                    if (sitePhotoRowAddFlag) {
                        cell = row.createCell(6);
                    } else {
                        cell = row.createCell(0);
                    }
                    // 创建一个图片对象
                    ClientAnchor anchor = helper.createClientAnchor();
                    anchor.setCol1(cell.getColumnIndex());
                    anchor.setRow1(cell.getRowIndex());
                    if (sitePhotoRowAddFlag) {
                        anchor.setCol2(cell.getColumnIndex() + 11 - 6);
                    } else {
                        anchor.setCol2(cell.getColumnIndex() + 5);
                    }
                    anchor.setRow2(cell.getRowIndex() + 15);
                    // 插入图片
                    drawing2.createPicture(anchor, pictureIdx);
                    if (sitePhotoRowAddFlag || i == sitePhoto.size() - 1) {
                        rowNum += 16;
                    }
                    sitePhotoRowAddFlag = !sitePhotoRowAddFlag;
                }
            }
        }


        // 将源文件的内容复制到目标文件
        workbook.write(out);
        // 关闭流
        out.close();
        inp.close();
        workbook.close();


        return outFileName;
    }

    private static InputStream getInputStream() throws FileNotFoundException {
        // 打开源文件
        return new FileInputStream(XlsConfig.XLS_NAME_MONTH);
    }

    public static String copyXlsYear(List<WbPlanClassifyYearVo> list, WbPlan wbPlan, boolean ifPicture, WbEntAuth wbEntAuth) throws IOException {
        if (CollectionUtils.isEmpty(list) || ObjectUtils.isEmpty(wbPlan)) {
            return "";
        }
        // 打开源文件
        InputStream inp = new FileInputStream(XlsConfig.XLS_NAME_YEAR);
        Workbook workbook = new XSSFWorkbook(inp);


        //标头样式
        CellStyle styleTitle = workbook.createCellStyle();
        //边框
        styleTitle.setBorderBottom(BorderStyle.THIN);
        styleTitle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styleTitle.setBorderRight(BorderStyle.THIN);
        styleTitle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        styleTitle.setBorderTop(BorderStyle.THIN);
        styleTitle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        styleTitle.setBorderLeft(BorderStyle.THIN);
        styleTitle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //字体
        Font fontTitle = workbook.createFont();
        fontTitle.setFontName("宋体"); // 设置字体
        fontTitle.setFontHeightInPoints((short) 10); // 设置字号
        styleTitle.setFont(fontTitle);
        styleTitle.setWrapText(true);//自动换行
        styleTitle.setAlignment(HorizontalAlignment.CENTER_SELECTION);  // 设置水平居中
        styleTitle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中


        CellStyle style = workbook.createCellStyle();
        //边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //字体
        Font font = workbook.createFont();
        font.setFontName("宋体"); // 设置字体
        font.setFontHeightInPoints((short) 10); // 设置字号
        style.setFont(font);
        style.setWrapText(true);//自动换行
        style.setAlignment(HorizontalAlignment.LEFT);  // 设置水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        String uuid = UUID.randomUUID().toString().replaceAll("-", "");
        String outFileName = uuid + ".xlsx";
        File folder = new File(XlsConfig.XLS_COPY_PATH_YEAR);
        if (!folder.exists()) {
            boolean mkdir = folder.mkdir();
            if (!mkdir) {
                throw new RuntimeException("创建文件副本目录失败");
            }
        }
        XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);
        //绘图工具
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        //背景色
        CellStyle cellStyle = workbook.createCellStyle();
        //边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //字体
        Font font1 = workbook.createFont();
        font1.setFontName("宋体"); // 设置字体
        font1.setFontHeightInPoints((short) 10); // 设置字号
        cellStyle.setFont(font1);
        cellStyle.setWrapText(true);//自动换行
        cellStyle.setAlignment(HorizontalAlignment.LEFT);  // 设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //填充内容
        int rowNum = sheet.getLastRowNum() + 1; //起始行


        //检测编号样式
        CellStyle item = workbook.createCellStyle();
        //字体
        Font itemfontTitle = workbook.createFont();
        itemfontTitle.setFontName("宋体"); // 设置字体
        itemfontTitle.setFontHeightInPoints((short) 10); // 设置字号
        item.setFont(itemfontTitle);
        item.setWrapText(true);//自动换行
        item.setAlignment(HorizontalAlignment.CENTER_SELECTION);  // 设置水平居中
        item.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中



        //首页填充字段样式
        CellStyle item2 = workbook.createCellStyle();
        //字体
        Font itemfontTitle2 = workbook.createFont();
        itemfontTitle2.setFontName("宋体"); // 设置字体
        itemfontTitle2.setFontHeightInPoints((short) 10); // 设置字号
        item2.setFont(itemfontTitle2);
        item2.setWrapText(true);//自动换行
        item2.setAlignment(HorizontalAlignment.CENTER_SELECTION);  // 设置水平居中
        item2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //边框
        item2.setBorderBottom(BorderStyle.THIN);
        item2.setBottomBorderColor(IndexedColors.BLACK.getIndex());

        //填充首页  和第二页内容
        for (int i = 0; i < rowNum; i++) {

            if (i == 1) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(7);
                cell.setCellStyle(item);
                cell.setCellValue(wbPlan.getDetectionNumber());
            }


            if (i == 10) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(4);
                cell.setCellStyle(item2);
                cell.setCellValue(StringUtils.isEmpty(wbPlan.getFacilityName())?"/":wbPlan.getFacilityName());
            }

            if (i == 11) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(4);
                cell.setCellStyle(item2);
                cell.setCellValue(StringUtils.isEmpty(wbPlan.getApplicationPlace())?"/":wbPlan.getApplicationPlace());
            }
            if (i == 12) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(4);
                cell.setCellStyle(item2);
                cell.setCellValue(StringUtils.isEmpty(wbPlan.getClientName())?"/":wbPlan.getClientName());
            }
            if (i == 13) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(4);
                cell.setCellStyle(item2);
                cell.setCellValue(StringUtils.isEmpty(wbPlan.getDetectCategory())?"/":wbPlan.getDetectCategory());
            }


            if (i == 22) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell0 = row.createCell(0);
                cell0.setCellStyle(styleTitle);
                cell0.setCellValue(wbPlan.getPlanName());
            }

            if (i == 23) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(7);
                cell.setCellStyle(styleTitle);
                cell.setCellValue(StringUtils.isEmpty(wbPlan.getDetectCategory())?"/":wbPlan.getDetectCategory());
            }

            if (i == 24) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell7 = row.createCell(7);
                cell7.setCellStyle(styleTitle);
                cell7.setCellValue(DateUtils.parseDateToStr("yyyy-MM-dd", DateUtils.getNowDate()));

                XSSFCell cell2 = row.createCell(2);
                cell2.setCellStyle(styleTitle);
                cell2.setCellValue(StringUtils.isEmpty(wbPlan.getApplicationPlace())?"/":wbPlan.getApplicationPlace());
            }
            if (i == 25) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell2 = row.createCell(2);
                cell2.setCellStyle(styleTitle);
                cell2.setCellValue(StringUtils.isEmpty(wbPlan.getApplicationPlaceAddr())?"/":wbPlan.getApplicationPlaceAddr());

                XSSFCell cell7 = row.createCell(7);
                cell7.setCellStyle(styleTitle);
                cell7.setCellValue(ObjectUtils.isEmpty(wbPlan.getReinspectionTime())?"/":DateUtils.parseDateToStr("yyyy-MM-dd",wbPlan.getReinspectionTime()));

            }
            if (i == 26) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell2 = row.createCell(2);
                cell2.setCellStyle(styleTitle);
                cell2.setCellValue(wbPlan.getClientName());

                XSSFCell cell7 = row.createCell(7);
                cell7.setCellStyle(styleTitle);
                cell7.setCellValue(StringUtils.isEmpty(wbPlan.getContactNumber())?"/":wbPlan.getContactNumber());

            }

            if (i == 27) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell2 = row.createCell(2);
                cell2.setCellStyle(styleTitle);
                cell2.setCellValue(StringUtils.isEmpty(wbPlan.getRecordNumber())?"/":wbPlan.getRecordNumber());

                XSSFCell cell7 = row.createCell(7);
                cell7.setCellStyle(styleTitle);
                cell7.setCellValue(ObjectUtils.isEmpty(wbPlan.getFilingTime())?"/":DateUtils.parseDateToStr("yyyy-MM-dd",wbPlan.getFilingTime()));
            }

            if (i == 29) {
                int k=2;
                XSSFRow row = sheet.getRow(i);
                String listOfFacilities = wbPlan.getListOfFacilities();
                JSONArray jsonArray = JSONUtil.parseArray(listOfFacilities);
                List<ListOfFacilitiesVo> listOfFacilitiesVos = jsonArray.toList(ListOfFacilitiesVo.class);
                StringBuilder stringBuilder = new StringBuilder();
                //标头样式
                CellStyle ic = workbook.createCellStyle();
                //边框
               ic.setBorderBottom(BorderStyle.THIN);
               ic.setBottomBorderColor(IndexedColors.BLACK.getIndex());
               ic.setBorderRight(BorderStyle.THIN);
               ic.setRightBorderColor(IndexedColors.BLACK.getIndex());
               ic.setBorderTop(BorderStyle.THIN);
               ic.setTopBorderColor(IndexedColors.BLACK.getIndex());
               ic.setBorderLeft(BorderStyle.THIN);
               ic.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                //字体
                Font ift = workbook.createFont();
                ift.setFontName("宋体"); // 设置字体
                ift.setFontHeightInPoints((short) 10); // 设置字号
                ic.setFont(ift);
                ic.setWrapText(true);//自动换行
                ic.setAlignment(HorizontalAlignment.LEFT);  // 设置水平居中
                ic.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                for (int j = 0; j <listOfFacilitiesVos.size(); j++) {
                    if ((j>0&&j%5==0)||j==listOfFacilitiesVos.size()-1){
                        XSSFCell cell = row.createCell(k);
                        cell.setCellStyle(ic);
                        cell.setCellValue(stringBuilder.toString());
                        k+=3;
                        stringBuilder.setLength(0);
                    }
                    stringBuilder.append(listOfFacilitiesVos.get(j).getValue().equals("1")?"■":"□").append(listOfFacilitiesVos.get(j).getText()).append("\n");
                }
            }

            if (i == 31) {
                String inventory = wbPlan.getInventory();
                if (!StringUtils.isEmpty(inventory)) {
                    XSSFRow row = sheet.getRow(i);
                    File engineerSig = new File(inventory.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
//                    BufferedImage image = ImageIO.read(engineerSig);
//                    int height = image.getHeight();
//                    row.setHeight((short) 7000);
                    if (engineerSig.exists()) {
                        //-----------建筑图片
                        byte[] operatorSigBytes = FileUtils.readFileToByteArray(engineerSig);
                        ClientAnchor operatorSigAnchor = drawing.createAnchor(0, 0, 0, 0, 2, i, 11, i + 1);

                        int operatorSigPictureIdx = workbook.addPicture(operatorSigBytes, Workbook.PICTURE_TYPE_JPEG);
                        // 插入图片 建筑图片
                        drawing.createPicture(operatorSigAnchor, operatorSigPictureIdx);
                    }
                }
            }
            if (i == 32) {
                Row row = sheet.getRow(i);
                String collect = wbPlan.getCollect();
                int i3 = StringUtils.isEmpty(collect) ? 1 : collect.length();
                int rowHigh = ((i3) / 20 + 1) * 10 * 15;
                row.setHeight((short) ((short) 80 + rowHigh));
                Cell cell1 = row.createCell(2);
                cell1.setCellStyle(styleTitle);
                cell1.setCellValue(collect);

            }
            if (i == 33) {
                Row row = sheet.getRow(i);
                String scheme = wbPlan.getScheme();
                int i5 = StringUtils.isEmpty(scheme) ? 1 : scheme.length();
                int rowHigh = ((i5) / 20 + 1) * 10 * 15;
                row.setHeight((short) ((short) 80 + rowHigh));
                Cell cell1 = row.createCell(2);
                cell1.setCellStyle(styleTitle);
                cell1.setCellValue(scheme);
            }
            if (i == 34) {
                //公司公章
                String commonSeal = wbPlan.getCommonSeal();
                //设置图片绘制的位置，以及范围
                // 创建一个绘图对象

                if (!StringUtils.isEmpty(commonSeal)) {
                    File engineerSig = new File(commonSeal.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                    if (engineerSig.exists()) {
//                        XSSFRow row = sheet.getRow(i);
//                        BufferedImage image = ImageIO.read(engineerSig);
//                        int height = image.getHeight();
//                        row.setHeight((short) (height*5));
                        //-----------维保公司印章
                        byte[] operatorSigBytes = FileUtils.readFileToByteArray(engineerSig);
                        ClientAnchor operatorSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, i, 12, i + 1);
                        int operatorSigPictureIdx = workbook.addPicture(operatorSigBytes, Workbook.PICTURE_TYPE_PNG);
                        // 插入图片 维保公司印章
                        drawing.createPicture(operatorSigAnchor, operatorSigPictureIdx);
                    }
                }
            }
            if (i == 35) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell8 = row.createCell(8);
                cell8.setCellStyle(styleTitle);
                cell8.setCellValue(DateUtils.getDate());
            }

            if (i==36){
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(2);
                cell.setCellStyle(styleTitle);
                String rests = wbPlan.getRests();
                row.setHeight((short) ((StringUtils.isEmpty(rests)?1:rests.length() /20+1)*15*20));
                cell.setCellValue(StringUtils.isEmpty(rests)?"无":rests);
            }
        }


        short lastCellNum = sheet.getRow(0).getLastCellNum();//结束列
        for (int i = 0; i < list.size(); i++) {
            //一级目录
            WbPlanClassifyYearVo wbPlanClassifyYearControllerVo = list.get(i);
            Row row = sheet.createRow(rowNum);
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                switch (j) {
                    case 0:
                        cell.setCellValue(extractSingleNumber(wbPlanClassifyYearControllerVo.getClassifyName()));
                        break;
                    case 2:
                        cell.setCellValue(wbPlanClassifyYearControllerVo.getClassifyName().replace(extractSingleNumber(wbPlanClassifyYearControllerVo.getClassifyName()),""));
                        break;
                }
            }
            sheet.addMergedRegion(new CellRangeAddress(
                    rowNum,
                    rowNum,
                    0,
                    1
            ));
            sheet.addMergedRegion(new CellRangeAddress(
                    rowNum,
                    rowNum,
                    2,
                    lastCellNum - 1
            ));
            rowNum += 1;
            //二级目录集合
            List<WbPlanClassifyYearContentVo> wbPlanClassifyYearContentVos = wbPlanClassifyYearControllerVo.getWbPlanClassifyYearContentVos();
            if (CollectionUtils.isEmpty(wbPlanClassifyYearContentVos)) {
                continue;
            }
            for (int j = 0; j < wbPlanClassifyYearContentVos.size(); j++) {
                //二级目录
                WbPlanClassifyYearContentVo wbPlanClassifyYearContentVo = wbPlanClassifyYearContentVos.get(j);
                if (StringUtils.isNotEmpty(wbPlanClassifyYearContentVo.getClassifyName())) {
                    Row row1 = sheet.createRow(rowNum);
                    int rowHigh = ((wbPlanClassifyYearContentVo.getClassifyName().length() + (StringUtils.isEmpty(wbPlanClassifyYearContentVo.getRemark()) ? 1 : wbPlanClassifyYearContentVo.getRemark().length())) / 20 + 1) * 15 * 20;
                    row1.setHeight((short) rowHigh);
                    for (int k = 0; k < lastCellNum; k++) {
                        Cell cell = row1.createCell(k);
                        cell.setCellStyle(cellStyle);
                        switch (k) {
                            case 0:
                                cell.setCellValue(extractSingleNumber(wbPlanClassifyYearContentVo.getClassifyName()));
                                break;
                            case 2:
                                cell.setCellValue(wbPlanClassifyYearContentVo.getClassifyName().replace(extractSingleNumber(wbPlanClassifyYearContentVo.getClassifyName()),"") + (StringUtils.isEmpty(wbPlanClassifyYearContentVo.getRemark()) ? "" : "    " + wbPlanClassifyYearContentVo.getRemark()));
                                break;
                        }
                    }
                    sheet.addMergedRegion(new CellRangeAddress(
                            rowNum,
                            rowNum,
                            0,
                            1
                    ));
                    sheet.addMergedRegion(new CellRangeAddress(
                            rowNum,
                            rowNum,
                            2,
                            lastCellNum - 1
                    ));
                    rowNum += 1;
                }
                //三级目录集合
                List<WbPlanClassifyYearContentThreeVo> wbPlanClassifyYearContentThreeVos = wbPlanClassifyYearContentVo.getWbPlanClassifyYearContentThreeVos();
                if (CollectionUtils.isEmpty(wbPlanClassifyYearContentThreeVos)) {
                    continue;
                }
                for (int n = 0; n < wbPlanClassifyYearContentThreeVos.size(); n++) {
                    //三级目录
                    WbPlanClassifyYearContentThreeVo wbPlanClassifyYearContentThreeVo = wbPlanClassifyYearContentThreeVos.get(n);
                    Row row1 = sheet.createRow(rowNum);
                    //设置行高
                    int rowHigh = ((Math.max(ObjectUtils.isEmpty(wbPlanClassifyYearContentThreeVo.getStandardRequest()) ? 1 : wbPlanClassifyYearContentThreeVo.getStandardRequest().length(), ObjectUtils.isEmpty(wbPlanClassifyYearContentThreeVo.getActualMeasurement()) ? 1 : wbPlanClassifyYearContentThreeVo.getActualMeasurement().length())) / 20 + 3) * 15 * 20;
                    row1.setHeight((short) rowHigh);
                    for (int k = 0; k < lastCellNum; k++) {
                        Cell cell = row1.createCell(k);
                        cell.setCellStyle(style);
                        switch (k) {
                            case 0:
                                cell.setCellValue((StringUtils.isNotEmpty(wbPlanClassifyYearContentThreeVo.getClassifyName()) ? wbPlanClassifyYearContentThreeVo.getClassifyName() : ""));
                                break;
                            case 2:
                                cell.setCellValue(wbPlanClassifyYearContentThreeVo.getStandardRequest());
                                break;
                            case 6:
                                cell.setCellValue(wbPlanClassifyYearContentThreeVo.getActualMeasurement() == null ? PlanTypeEnums.UNDETECTED.getDescribe() : wbPlanClassifyYearContentThreeVo.getActualMeasurement());
                                break;
                            case 10:
                                cell.setCellValue(wbPlanClassifyYearContentThreeVo.getResult() == null||wbPlanClassifyYearContentThreeVo.getResult()==3 ? PlanTypeEnums.UNDETECTED.getDescribe() : wbPlanClassifyYearContentThreeVo.getResult().toString().equals(PlanTypeEnums.ELIGBLE.getType()) ? PlanTypeEnums.ELIGBLE.getDescribe() : PlanTypeEnums.UNQUALIFIED.getDescribe());
                                break;
                        }
                    }
                    sheet.addMergedRegion(new CellRangeAddress(
                            rowNum,
                            rowNum,
                            0,
                            1
                    ));
                    sheet.addMergedRegion(new CellRangeAddress(
                            rowNum,
                            rowNum,
                            2,
                            5
                    ));
                    sheet.addMergedRegion(new CellRangeAddress(
                            rowNum,
                            rowNum,
                            6,
                            9
                    ));
                    sheet.addMergedRegion(new CellRangeAddress(
                            rowNum,
                            rowNum,
                            10,
                            11
                    ));
                    rowNum += 1;
                }
            }
        }


        //填充页尾
        Row endRow = sheet.createRow(rowNum);
        endRow.setHeight((short) (40 * 20));
        for (int i = 0; i < lastCellNum; i++) {
            Cell cell = endRow.createCell(i);
            cell.setCellStyle(style);
            switch (i) {
                case 0:
                    cell.setCellValue("备注");
                    break;
                case 2:
                    cell.setCellValue("  检测报告中为“------”代表系统无此项或有此项未委托检测。 \n" +
                            " （以下空白）");
                    break;
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum,
                rowNum,
                0,
                1
        ));
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum,
                rowNum,
                2,
                lastCellNum - 1
        ));
        rowNum += 1;

        //是否绘制图片
        if (ifPicture) {
            //设置图片绘制的位置，以及范围
            // 创建一个绘图对象
            CreationHelper helper = workbook.getCreationHelper();


            //拿到图片
            CellStyle pngStyle = workbook.createCellStyle();
            pngStyle.setAlignment(HorizontalAlignment.CENTER);  // 设置水平居中
            pngStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            pngStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            pngStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);

            //公司logo
            String commonLogo = wbPlan.getCommonLogo();

            if (!StringUtils.isEmpty(commonLogo)) {
                File engineerSig = new File(commonLogo.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                if (engineerSig.exists()) {
                    //-----------维保公司logo
                    byte[] operatorSigBytes = FileUtils.readFileToByteArray(engineerSig);
                    ClientAnchor operatorSigAnchor = drawing.createAnchor(0, 0, 0, 0, 2, 1, 4, 3);

                    int operatorSigPictureIdx = workbook.addPicture(operatorSigBytes, Workbook.PICTURE_TYPE_PNG);
                    // 插入图片 维保公司logo
                    drawing.createPicture(operatorSigAnchor, operatorSigPictureIdx);
                }
            }



            if (StringUtils.isNotEmpty(wbPlan.getEntrySlip())) {
                List<String> entrySlip = Arrays.asList(wbPlan.getEntrySlip().split(","));
                boolean entrySlipRowAddFlag = false;
                //凭条
                Row pngRow = sheet.createRow(rowNum);
                Cell pngCell = pngRow.createCell(0);
                pngCell.setCellValue("现场检查凭条");
                pngCell.setCellStyle(pngStyle);
                sheet.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        0,
                        11
                ));
                rowNum += 1;
                for (int i = 0; i < entrySlip.size(); i++) {
                    File file = new File(entrySlip.get(i).replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                    if (!file.exists()) {
                        if (i == entrySlip.size() - 1) {
                            rowNum += 16;
                        }
                        continue;
                    }
                    byte[] bytes = FileUtils.readFileToByteArray(file);
                    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                    Row row = null;
                    if (entrySlipRowAddFlag) {
                        row = sheet.getRow(rowNum);
                    } else {
                        row = sheet.createRow(rowNum);
                    }
                    Cell cell = null;
                    if (entrySlipRowAddFlag) {
                        cell = row.createCell(6);
                    } else {
                        cell = row.createCell(0);
                    }
                    // 创建一个图片对象
                    ClientAnchor anchor = helper.createClientAnchor();
                    anchor.setCol1(cell.getColumnIndex());
                    anchor.setRow1(cell.getRowIndex());
                    if (entrySlipRowAddFlag) {
                        anchor.setCol2(cell.getColumnIndex() + 6);
                    } else {
                        anchor.setCol2(cell.getColumnIndex() + 6);
                    }
                    anchor.setRow2(cell.getRowIndex() + 15);
                    // 插入图片
                    drawing.createPicture(anchor, pictureIdx);
                    if (entrySlipRowAddFlag || i == entrySlip.size() - 1) {
                        rowNum += 16;
                    }
                    entrySlipRowAddFlag = !entrySlipRowAddFlag;
                }
            }


            if (StringUtils.isNotEmpty(wbPlan.getSitePhoto())) {
                List<String> sitePhoto = Arrays.asList(wbPlan.getSitePhoto().split(","));
                boolean sitePhotoRowAddFlag = false;
                //图片
                Row pngRow = sheet.createRow(rowNum);
                Cell pngCell = pngRow.createCell(0);
                pngCell.setCellStyle(pngStyle);
                pngCell.setCellValue("现场检查照片");
                sheet.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        0,
                        11
                ));
                rowNum += 1;
                for (int i = 0; i < sitePhoto.size(); i++) {
                    File file = new File(sitePhoto.get(i).replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                    if (!file.exists()) {
                        continue;
                    }
                    byte[] bytes = FileUtils.readFileToByteArray(file);
                    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                    Row row = null;
                    if (sitePhotoRowAddFlag) {
                        row = sheet.getRow(rowNum);
                    } else {
                        row = sheet.createRow(rowNum);
                    }
                    Cell cell = null;
                    if (sitePhotoRowAddFlag) {
                        cell = row.createCell(6);
                    } else {
                        cell = row.createCell(0);
                    }
                    // 创建一个图片对象
                    ClientAnchor anchor = helper.createClientAnchor();
                    anchor.setCol1(cell.getColumnIndex());
                    anchor.setRow1(cell.getRowIndex());
                    if (sitePhotoRowAddFlag) {
                        anchor.setCol2(cell.getColumnIndex() + 6);
                    } else {
                        anchor.setCol2(cell.getColumnIndex() + 6);
                    }
                    anchor.setRow2(cell.getRowIndex() + 15);
                    // 插入图片
                    drawing.createPicture(anchor, pictureIdx);
                    if (sitePhotoRowAddFlag || i == sitePhoto.size() - 1) {
                        rowNum += 16;
                    }
                    sitePhotoRowAddFlag = !sitePhotoRowAddFlag;
                }
            }
        }


        // 创建目标文件
        FileOutputStream out = new FileOutputStream(XlsConfig.XLS_COPY_PATH_YEAR + outFileName);
        // 将源文件的内容复制到目标文件
        workbook.write(out);
        // 关闭流
        out.close();
        inp.close();
        workbook.close();

        copyXlsYear2(list,wbPlan,ifPicture,wbEntAuth,uuid);

        return outFileName;
    }

    public static String copyXlsYear2(List<WbPlanClassifyYearVo> list, WbPlan wbPlan, boolean ifPicture, WbEntAuth wbEntAuth,String uuid) throws IOException {
        if (CollectionUtils.isEmpty(list) || ObjectUtils.isEmpty(wbPlan)) {
            return "";
        }
        // 打开源文件
        InputStream inp = new FileInputStream(XlsConfig.XLS_NAME_YEAR);
        Workbook workbook = new XSSFWorkbook(inp);


        //标头样式
        CellStyle styleTitle = workbook.createCellStyle();
        //边框
        styleTitle.setBorderBottom(BorderStyle.THIN);
        styleTitle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styleTitle.setBorderRight(BorderStyle.THIN);
        styleTitle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        styleTitle.setBorderTop(BorderStyle.THIN);
        styleTitle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        styleTitle.setBorderLeft(BorderStyle.THIN);
        styleTitle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //字体
        Font fontTitle = workbook.createFont();
        fontTitle.setFontName("宋体"); // 设置字体
        fontTitle.setFontHeightInPoints((short) 10); // 设置字号
        styleTitle.setFont(fontTitle);
        styleTitle.setWrapText(true);//自动换行
        styleTitle.setAlignment(HorizontalAlignment.CENTER_SELECTION);  // 设置水平居中
        styleTitle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中


        CellStyle style = workbook.createCellStyle();
        //边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //字体
        Font font = workbook.createFont();
        font.setFontName("宋体"); // 设置字体
        font.setFontHeightInPoints((short) 10); // 设置字号
        style.setFont(font);
        style.setWrapText(true);//自动换行
        style.setAlignment(HorizontalAlignment.LEFT);  // 设置水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        String outFileName = uuid + "split.xlsx";
        File folder = new File(XlsConfig.XLS_COPY_PATH_YEAR);
        if (!folder.exists()) {
            boolean mkdir = folder.mkdir();
            if (!mkdir) {
                throw new RuntimeException("创建文件副本目录失败");
            }
        }
        XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);
        //绘图工具
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        //背景色
        CellStyle cellStyle = workbook.createCellStyle();
        //边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //字体
        Font font1 = workbook.createFont();
        font1.setFontName("宋体"); // 设置字体
        font1.setFontHeightInPoints((short) 10); // 设置字号
        cellStyle.setFont(font1);
        cellStyle.setWrapText(true);//自动换行
        cellStyle.setAlignment(HorizontalAlignment.LEFT);  // 设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //填充内容
        int rowNum = sheet.getLastRowNum() + 1; //起始行




        //标头样式
        CellStyle item = workbook.createCellStyle();
        //字体
        Font itemfontTitle = workbook.createFont();
        itemfontTitle.setFontName("宋体"); // 设置字体
        itemfontTitle.setFontHeightInPoints((short) 10); // 设置字号
        item.setFont(itemfontTitle);
        item.setWrapText(true);//自动换行
        item.setAlignment(HorizontalAlignment.CENTER_SELECTION);  // 设置水平居中
        item.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中



        //标头样式
        CellStyle item2 = workbook.createCellStyle();
        //字体
        Font itemfontTitle2 = workbook.createFont();
        itemfontTitle2.setFontName("宋体"); // 设置字体
        itemfontTitle2.setFontHeightInPoints((short) 10); // 设置字号
        item2.setFont(itemfontTitle2);
        item2.setWrapText(true);//自动换行
        item2.setAlignment(HorizontalAlignment.CENTER_SELECTION);  // 设置水平居中
        item2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //边框
        item2.setBorderBottom(BorderStyle.THIN);
        item2.setBottomBorderColor(IndexedColors.BLACK.getIndex());



        //填充首页  和第二页内容
        for (int i = 0; i < rowNum; i++) {

            if (i == 1) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell7 = row.createCell(7);
                cell7.setCellStyle(item);
                cell7.setCellValue(wbPlan.getDetectionNumber());
            }


            if (i == 10) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(4);
                cell.setCellStyle(item2);
                cell.setCellValue(StringUtils.isEmpty(wbPlan.getFacilityName())?"/":wbPlan.getFacilityName());
            }

            if (i == 11) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(4);
                cell.setCellStyle(item2);
                cell.setCellValue(StringUtils.isEmpty(wbPlan.getApplicationPlace())?"/":wbPlan.getApplicationPlace());
            }
            if (i == 12) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(4);
                cell.setCellStyle(item2);
                cell.setCellValue(StringUtils.isEmpty(wbPlan.getClientName())?"/":wbPlan.getClientName());
            }
            if (i == 13) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(4);
                cell.setCellStyle(item2);
                cell.setCellValue(StringUtils.isEmpty(wbPlan.getDetectCategory())?"/":wbPlan.getDetectCategory());
            }


            if (i == 22) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell0 = row.createCell(0);
                cell0.setCellStyle(styleTitle);
                cell0.setCellValue(wbPlan.getPlanName());
            }

            if (i == 23) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(7);
                cell.setCellStyle(styleTitle);
                cell.setCellValue(StringUtils.isEmpty(wbPlan.getDetectCategory())?"/":wbPlan.getDetectCategory());
            }

            if (i == 24) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell7 = row.createCell(7);
                cell7.setCellStyle(styleTitle);
                cell7.setCellValue(DateUtils.parseDateToStr("yyyy-MM-dd", DateUtils.getNowDate()));

                XSSFCell cell2 = row.createCell(2);
                cell2.setCellStyle(styleTitle);
                cell2.setCellValue(StringUtils.isEmpty(wbPlan.getApplicationPlace())?"/":wbPlan.getApplicationPlace());
            }
            if (i == 25) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell2 = row.createCell(2);
                cell2.setCellStyle(styleTitle);
                cell2.setCellValue(StringUtils.isEmpty(wbPlan.getApplicationPlaceAddr())?"/":wbPlan.getApplicationPlaceAddr());

                XSSFCell cell7 = row.createCell(7);
                cell7.setCellStyle(styleTitle);
                cell7.setCellValue(ObjectUtils.isEmpty(wbPlan.getReinspectionTime())?"/":DateUtils.parseDateToStr("yyyy-MM-dd",wbPlan.getReinspectionTime()));

            }
            if (i == 26) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell2 = row.createCell(2);
                cell2.setCellStyle(styleTitle);
                cell2.setCellValue(wbPlan.getClientName());

                XSSFCell cell7 = row.createCell(7);
                cell7.setCellStyle(styleTitle);
                cell7.setCellValue(StringUtils.isEmpty(wbPlan.getContactNumber())?"/":wbPlan.getContactNumber());

            }

            if (i == 27) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell2 = row.createCell(2);
                cell2.setCellStyle(styleTitle);
                cell2.setCellValue(StringUtils.isEmpty(wbPlan.getRecordNumber())?"/":wbPlan.getRecordNumber());

                XSSFCell cell7 = row.createCell(7);
                cell7.setCellStyle(styleTitle);
                cell7.setCellValue(ObjectUtils.isEmpty(wbPlan.getFilingTime())?"/":DateUtils.parseDateToStr("yyyy-MM-dd",wbPlan.getFilingTime()));
            }

            if (i == 29) {
                int k=2;
                XSSFRow row = sheet.getRow(i);
                String listOfFacilities = wbPlan.getListOfFacilities();
                JSONArray jsonArray = JSONUtil.parseArray(listOfFacilities);
                List<ListOfFacilitiesVo> listOfFacilitiesVos = jsonArray.toList(ListOfFacilitiesVo.class);
                StringBuilder stringBuilder = new StringBuilder();
                //标头样式
                CellStyle ic = workbook.createCellStyle();
                //边框
                ic.setBorderBottom(BorderStyle.THIN);
                ic.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                ic.setBorderRight(BorderStyle.THIN);
                ic.setRightBorderColor(IndexedColors.BLACK.getIndex());
                ic.setBorderTop(BorderStyle.THIN);
                ic.setTopBorderColor(IndexedColors.BLACK.getIndex());
                ic.setBorderLeft(BorderStyle.THIN);
                ic.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                //字体
                Font ift = workbook.createFont();
                ift.setFontName("宋体"); // 设置字体
                ift.setFontHeightInPoints((short) 10); // 设置字号
                ic.setFont(ift);
                ic.setWrapText(true);//自动换行
                ic.setAlignment(HorizontalAlignment.LEFT);  // 设置水平居中
                ic.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                for (int j = 0; j <listOfFacilitiesVos.size(); j++) {
                    if ((j>0&&j%5==0)||j==listOfFacilitiesVos.size()-1){
                        XSSFCell cell = row.createCell(k);
                        cell.setCellStyle(ic);
                        cell.setCellValue(stringBuilder.toString());
                        k+=3;
                        stringBuilder.setLength(0);
                    }
                    stringBuilder.append(listOfFacilitiesVos.get(j).getValue().equals("1")?"■":"□").append(listOfFacilitiesVos.get(j).getText()).append("\n");
                }
            }

            if (i == 31) {
                String inventory = wbPlan.getInventory();
                if (!StringUtils.isEmpty(inventory)) {
                    File engineerSig = new File(inventory.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                    if (engineerSig.exists()) {
                        //-----------建筑图片
                        byte[] operatorSigBytes = FileUtils.readFileToByteArray(engineerSig);
                        ClientAnchor operatorSigAnchor = drawing.createAnchor(0, 0, 0, 0, 2, i, 11, i + 1);
                        int operatorSigPictureIdx = workbook.addPicture(operatorSigBytes, Workbook.PICTURE_TYPE_JPEG);
                        // 插入图片 建筑图片
                        drawing.createPicture(operatorSigAnchor, operatorSigPictureIdx);
                    }
                }
            }
            if (i == 32) {
                Row row = sheet.getRow(i);
                String collect = wbPlan.getCollect();
                int i3 = StringUtils.isEmpty(collect) ? 1 : collect.length();
                int rowHigh = ((i3) / 20 + 1) * 10 * 15;
                row.setHeight((short) ((short) 80 + rowHigh));
                Cell cell1 = row.createCell(2);
                cell1.setCellStyle(styleTitle);
                cell1.setCellValue(collect);

            }
            if (i == 33) {
                Row row = sheet.getRow(i);
                String scheme = wbPlan.getScheme();
                int i5 = StringUtils.isEmpty(scheme) ? 1 : scheme.length();
                int rowHigh = ((i5) / 20 + 1) * 10 * 15;
                row.setHeight((short) ((short) 80 + rowHigh));
                Cell cell1 = row.createCell(2);
                cell1.setCellStyle(styleTitle);
                cell1.setCellValue(scheme);
            }
            if (i == 34) {
                //公司公章
                String commonSeal = wbPlan.getCommonSeal();
                //设置图片绘制的位置，以及范围
                // 创建一个绘图对象

                if (!StringUtils.isEmpty(commonSeal)) {
                    File engineerSig = new File(commonSeal.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                    if (engineerSig.exists()) {
//                        XSSFRow row = sheet.getRow(i);
//                        BufferedImage image = ImageIO.read(engineerSig);
//                        int height = image.getHeight();
//                        row.setHeight((short) (height*5));
                        //-----------维保公司印章
                        byte[] operatorSigBytes = FileUtils.readFileToByteArray(engineerSig);
                        ClientAnchor operatorSigAnchor = drawing.createAnchor(0, 0, 0, 0, 8, i, 12, i + 1);
                        int operatorSigPictureIdx = workbook.addPicture(operatorSigBytes, Workbook.PICTURE_TYPE_PNG);
                        // 插入图片 维保公司印章
                        drawing.createPicture(operatorSigAnchor, operatorSigPictureIdx);
                    }
                }
            }
            if (i == 35) {
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell8 = row.createCell(8);
                cell8.setCellStyle(styleTitle);
                cell8.setCellValue(DateUtils.getDate());
            }

            if (i==36){
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell = row.createCell(2);
                cell.setCellStyle(styleTitle);
                String rests = wbPlan.getRests();
                row.setHeight((short) ((StringUtils.isEmpty(rests)?1:rests.length() /20+1)*15*20));
                cell.setCellValue(StringUtils.isEmpty(rests)?"无":rests);
            }

        }
        short lastCellNum = sheet.getRow(0).getLastCellNum();//结束列
        for (int i = 0; i < list.size(); i++) {
            //一级目录
            WbPlanClassifyYearVo wbPlanClassifyYearControllerVo = list.get(i);
            Row row = sheet.createRow(rowNum);
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                switch (j) {
                    case 0:
                        cell.setCellValue(extractSingleNumber(wbPlanClassifyYearControllerVo.getClassifyName()));
                        break;
                    case 2:
                        cell.setCellValue(wbPlanClassifyYearControllerVo.getClassifyName().replace(extractSingleNumber(wbPlanClassifyYearControllerVo.getClassifyName()),""));
                        break;
                }
            }
            sheet.addMergedRegion(new CellRangeAddress(
                    rowNum,
                    rowNum,
                    0,
                    1
            ));
            sheet.addMergedRegion(new CellRangeAddress(
                    rowNum,
                    rowNum,
                    2,
                    lastCellNum - 1
            ));
            rowNum += 1;
            //二级目录集合
            List<WbPlanClassifyYearContentVo> wbPlanClassifyYearContentVos = wbPlanClassifyYearControllerVo.getWbPlanClassifyYearContentVos();
            if (CollectionUtils.isEmpty(wbPlanClassifyYearContentVos)) {
                continue;
            }
            for (int j = 0; j < wbPlanClassifyYearContentVos.size(); j++) {
                //二级目录
                WbPlanClassifyYearContentVo wbPlanClassifyYearContentVo = wbPlanClassifyYearContentVos.get(j);
                if (StringUtils.isNotEmpty(wbPlanClassifyYearContentVo.getClassifyName())) {
                    Row row1 = sheet.createRow(rowNum);
                    int rowHigh = ((wbPlanClassifyYearContentVo.getClassifyName().length() + (StringUtils.isEmpty(wbPlanClassifyYearContentVo.getRemark()) ? 1 : wbPlanClassifyYearContentVo.getRemark().length())) / 20 + 1) * 15 * 20;
                    row1.setHeight((short) rowHigh);
                    for (int k = 0; k < lastCellNum; k++) {
                        Cell cell = row1.createCell(k);
                        cell.setCellStyle(cellStyle);
                        switch (k) {
                            case 0:
                                cell.setCellValue(extractSingleNumber(wbPlanClassifyYearContentVo.getClassifyName()));
                                break;
                            case 2:
                                cell.setCellValue(wbPlanClassifyYearContentVo.getClassifyName().replace(extractSingleNumber(wbPlanClassifyYearContentVo.getClassifyName()),"") + (StringUtils.isEmpty(wbPlanClassifyYearContentVo.getRemark()) ? "" : "    " + wbPlanClassifyYearContentVo.getRemark()));
                                break;
                        }
                    }
                    sheet.addMergedRegion(new CellRangeAddress(
                            rowNum,
                            rowNum,
                            0,
                            1
                    ));
                    sheet.addMergedRegion(new CellRangeAddress(
                            rowNum,
                            rowNum,
                            2,
                            lastCellNum - 1
                    ));
                    rowNum += 1;
                }
                //三级目录集合
                List<WbPlanClassifyYearContentThreeVo> wbPlanClassifyYearContentThreeVos = wbPlanClassifyYearContentVo.getWbPlanClassifyYearContentThreeVos();
                if (CollectionUtils.isEmpty(wbPlanClassifyYearContentThreeVos)) {
                    continue;
                }
                for (int n = 0; n < wbPlanClassifyYearContentThreeVos.size(); n++) {
                    //三级目录
                    WbPlanClassifyYearContentThreeVo wbPlanClassifyYearContentThreeVo = wbPlanClassifyYearContentThreeVos.get(n);
                    Row row1 = sheet.createRow(rowNum);
                    //设置行高
                    int rowHigh = ((Math.max(ObjectUtils.isEmpty(wbPlanClassifyYearContentThreeVo.getStandardRequest()) ? 1 : wbPlanClassifyYearContentThreeVo.getStandardRequest().length(), ObjectUtils.isEmpty(wbPlanClassifyYearContentThreeVo.getActualMeasurement()) ? 1 : wbPlanClassifyYearContentThreeVo.getActualMeasurement().length())) / 20 + 3) * 15 * 20;
                    row1.setHeight((short) rowHigh);
                    for (int k = 0; k < lastCellNum; k++) {
                        Cell cell = row1.createCell(k);
                        cell.setCellStyle(style);
                        switch (k) {
                            case 0:
                                cell.setCellValue((StringUtils.isNotEmpty(wbPlanClassifyYearContentThreeVo.getClassifyName()) ? wbPlanClassifyYearContentThreeVo.getClassifyName() : ""));
                                break;
                            case 2:
                                cell.setCellValue(wbPlanClassifyYearContentThreeVo.getStandardRequest());
                                break;
                            case 6:
                                cell.setCellValue(wbPlanClassifyYearContentThreeVo.getActualMeasurement() == null ? PlanTypeEnums.UNDETECTED.getDescribe() : wbPlanClassifyYearContentThreeVo.getActualMeasurement());
                                break;
                            case 10:
                                cell.setCellValue(wbPlanClassifyYearContentThreeVo.getResult() == null||wbPlanClassifyYearContentThreeVo.getResult()==3 ? PlanTypeEnums.UNDETECTED.getDescribe() : wbPlanClassifyYearContentThreeVo.getResult().toString().equals(PlanTypeEnums.ELIGBLE.getType()) ? PlanTypeEnums.ELIGBLE.getDescribe() : PlanTypeEnums.UNQUALIFIED.getDescribe());
                                break;
                        }
                    }
                    sheet.addMergedRegion(new CellRangeAddress(
                            rowNum,
                            rowNum,
                            0,
                            1
                    ));
                    sheet.addMergedRegion(new CellRangeAddress(
                            rowNum,
                            rowNum,
                            2,
                            5
                    ));
                    sheet.addMergedRegion(new CellRangeAddress(
                            rowNum,
                            rowNum,
                            6,
                            9
                    ));
                    sheet.addMergedRegion(new CellRangeAddress(
                            rowNum,
                            rowNum,
                            10,
                            11
                    ));
                    rowNum += 1;
                }
            }
        }






        //填充页尾
        Row endRow = sheet.createRow(rowNum);
        endRow.setHeight((short) (40 * 20));
        for (int i = 0; i < lastCellNum; i++) {
            Cell cell = endRow.createCell(i);
            cell.setCellStyle(style);
            switch (i) {
                case 0:
                    cell.setCellValue("备注");
                    break;
                case 2:
                    cell.setCellValue("  检测报告中为“------”代表系统无此项或有此项未委托检测。 \n" +
                            " （以下空白）");
                    break;
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum,
                rowNum,
                0,
                1
        ));
        sheet.addMergedRegion(new CellRangeAddress(
                rowNum,
                rowNum,
                2,
                lastCellNum - 1
        ));
        rowNum += 1;

        //是否绘制图片
        if (ifPicture) {
            //设置图片绘制的位置，以及范围
            // 创建一个绘图对象
            CreationHelper helper = workbook.getCreationHelper();


            //拿到图片
            CellStyle pngStyle = workbook.createCellStyle();
            pngStyle.setAlignment(HorizontalAlignment.CENTER);  // 设置水平居中
            pngStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            pngStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            pngStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);

            //公司logo
            String commonLogo = wbPlan.getCommonLogo();

            if (!StringUtils.isEmpty(commonLogo)) {
                File engineerSig = new File(commonLogo.replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                if (engineerSig.exists()) {
                    //-----------维保公司logo
                    byte[] operatorSigBytes = FileUtils.readFileToByteArray(engineerSig);
                    ClientAnchor operatorSigAnchor = drawing.createAnchor(0, 0, 0, 0, 2, 1, 4, 3);

                    int operatorSigPictureIdx = workbook.addPicture(operatorSigBytes, Workbook.PICTURE_TYPE_PNG);
                    // 插入图片 维保公司logo
                    drawing.createPicture(operatorSigAnchor, operatorSigPictureIdx);
                }
            }



            if (StringUtils.isNotEmpty(wbPlan.getEntrySlip())) {
                rowNum=0;
                Sheet sheet1 = workbook.getSheetAt(1);
                Drawing<?> drawing1 = sheet1.createDrawingPatriarch();
                List<String> entrySlip = Arrays.asList(wbPlan.getEntrySlip().split(","));
                boolean entrySlipRowAddFlag = false;
                //凭条
                Row pngRow = sheet1.createRow(rowNum);
                Cell pngCell = pngRow.createCell(0);
                pngCell.setCellValue("现场检查凭条");
                pngCell.setCellStyle(pngStyle);
                sheet1.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        0,
                        11
                ));
                rowNum += 1;
                for (int i = 0; i < entrySlip.size(); i++) {
                    File file = new File(entrySlip.get(i).replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                    if (!file.exists()) {
                        if (i == entrySlip.size() - 1) {
                            rowNum += 16;
                        }
                        continue;
                    }
                    byte[] bytes = FileUtils.readFileToByteArray(file);
                    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                    Row row = null;
                    if (entrySlipRowAddFlag) {
                        row = sheet1.getRow(rowNum);
                    } else {
                        row = sheet1.createRow(rowNum);
                    }
                    Cell cell = null;
                    if (entrySlipRowAddFlag) {
                        cell = row.createCell(6);
                    } else {
                        cell = row.createCell(0);
                    }
                    // 创建一个图片对象
                    ClientAnchor anchor = helper.createClientAnchor();
                    anchor.setCol1(cell.getColumnIndex());
                    anchor.setRow1(cell.getRowIndex());
                    if (entrySlipRowAddFlag) {
                        anchor.setCol2(cell.getColumnIndex() + 6);
                    } else {
                        anchor.setCol2(cell.getColumnIndex() + 6);
                    }
                    anchor.setRow2(cell.getRowIndex() + 15);
                    // 插入图片
                    drawing1.createPicture(anchor, pictureIdx);
                    if (entrySlipRowAddFlag || i == entrySlip.size() - 1) {
                        rowNum += 16;
                    }
                    entrySlipRowAddFlag = !entrySlipRowAddFlag;
                }
            }


            if (StringUtils.isNotEmpty(wbPlan.getSitePhoto())) {
                rowNum=0;
                List<String> sitePhoto = Arrays.asList(wbPlan.getSitePhoto().split(","));
                boolean sitePhotoRowAddFlag = false;
                //图片
                Sheet sheet2 = workbook.getSheetAt(2);
                Row pngRow = sheet2.createRow(rowNum);
                Drawing<?> drawing2 = sheet2.createDrawingPatriarch();
                Cell pngCell = pngRow.createCell(0);
                pngCell.setCellStyle(pngStyle);
                pngCell.setCellValue("现场检查照片");
                sheet2.addMergedRegion(new CellRangeAddress(
                        rowNum,
                        rowNum,
                        0,
                        11
                ));
                rowNum += 1;
                for (int i = 0; i < sitePhoto.size(); i++) {
                    File file = new File(sitePhoto.get(i).replaceAll(XlsConfig.PROFILE, RuoYiConfig.getProfile()));
                    if (!file.exists()) {
                        continue;
                    }
                    byte[] bytes = FileUtils.readFileToByteArray(file);
                    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                    Row row = null;
                    if (sitePhotoRowAddFlag) {
                        row = sheet2.getRow(rowNum);
                    } else {
                        row = sheet2.createRow(rowNum);
                    }
                    Cell cell = null;
                    if (sitePhotoRowAddFlag) {
                        cell = row.createCell(6);
                    } else {
                        cell = row.createCell(0);
                    }
                    // 创建一个图片对象
                    ClientAnchor anchor = helper.createClientAnchor();
                    anchor.setCol1(cell.getColumnIndex());
                    anchor.setRow1(cell.getRowIndex());
                    if (sitePhotoRowAddFlag) {
                        anchor.setCol2(cell.getColumnIndex() + 6);
                    } else {
                        anchor.setCol2(cell.getColumnIndex() + 6);
                    }
                    anchor.setRow2(cell.getRowIndex() + 15);
                    // 插入图片
                    drawing2.createPicture(anchor, pictureIdx);
                    if (sitePhotoRowAddFlag || i == sitePhoto.size() - 1) {
                        rowNum += 16;
                    }
                    sitePhotoRowAddFlag = !sitePhotoRowAddFlag;
                }
            }
        }


        // 创建目标文件
        FileOutputStream out = new FileOutputStream(XlsConfig.XLS_COPY_PATH_YEAR + outFileName);
        // 将源文件的内容复制到目标文件
        workbook.write(out);
        // 关闭流
        out.close();
        inp.close();
        workbook.close();
        return outFileName;
    }

//        public static String xlsToPdf (String xlsName) throws IOException {
//        com.spire.xls.Workbook wb = new com.spire.xls.Workbook();
//        wb.loadFromFile(xlsName);
//        //获取到哪一个sheet页
//        Worksheet sheet = wb.getWorksheets().get(0);
//        //随机起一个名称
//        String pdfName =xlsName.substring(0,xlsName.length()-4) + ".pdf";
//        //调用方法保存为PDF格式
//        sheet.saveToPdf(pdfName);
//
//        return  pdfName;
//    }

    public static String xlsToPng(String xlsName, String type) {
        if (StringUtils.isEmpty(xlsName)) {
            return "";
        }

        if (StringUtils.isEmpty(type)) {
            throw new RuntimeException("计划类型为空");
        }

        String tempUrl = type.equals(PlanTypeEnums.MONTHLY.getType()) ? XlsConfig.XLS_COPY_PATH_MONTH : XlsConfig.XLS_COPY_PATH_YEAR;

        com.spire.xls.Workbook wb = new com.spire.xls.Workbook();
        File folder = new File(tempUrl);
        if (!folder.exists()) {
            boolean mkdir = folder.mkdir();
            if (!mkdir) {
                throw new RuntimeException("创建图片目录失败");
            }
        }

        //位置所在位置
        wb.loadFromFile(tempUrl + xlsName);
        Worksheet sheet = wb.getWorksheets().get(0);

        // 设置缩放比例
        sheet.getPageSetup().setZoom(120);

        String pngName = xlsName.substring(0, xlsName.length() - 5) + ".png";

        sheet.saveToImage(tempUrl + pngName);
        return pngName;
    }

    public static String xlsToPdf(String xlsName, String type) throws Exception {
//        if (StringUtils.isEmpty(xlsName)) {
//            return "";
//        }
//        String tempUrl = type.equals(PlanTypeEnums.MONTHLY.getType()) ? XlsConfig.XLS_COPY_PATH_MONTH : XlsConfig.XLS_COPY_PATH_YEAR;
//
//        File folder = new File(tempUrl);
//        if (!folder.exists()) {
//            boolean mkdir = folder.mkdir();
//            if (!mkdir) {
//                throw new RuntimeException("创建图片目录失败");
//            }
//        }
//        String pdfName = xlsName.substring(0, xlsName.length() - 5) + ".pdf";
//
//        License license = new License();
//        license.setLicense(XlsConfig.CRACK_KEY);
//
//        com.aspose.cells.Workbook wb = new com.aspose.cells.Workbook(tempUrl + xlsName);
////        for (int i = 0; i < wb.getWorksheets().getCount(); i++) {
////            com.aspose.cells.Worksheet worksheet = wb.getWorksheets().get(i);
////            PageSetup pageSetup = worksheet.getPageSetup();
////            pageSetup.setPaperSize(PaperSizeType.PAPER_A_4); // 设置纸张大小为A4
////        }
//        PdfSaveOptions options = new PdfSaveOptions();
//        options.setAllColumnsInOnePagePerSheet(true);
//        options.setOnePagePerSheet(true);
//        wb.save(tempUrl + pdfName, SaveFormat.PDF);
//        wb.dispose();
//        return pdfName;
        return "";
    }

    /**
     * 提取字符串中的单个数字或小数。
     * 假设输入字符串只包含一个数字或小数。
     * @param input 输入的字符串
     * @return 提取出的数字或小数（字符串形式）
     */
    public static String extractSingleNumber(String input) {
        // 正则表达式，匹配整数和小数
        String regex = "\\d+\\.\\d+|\\d+";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(input);

        if (matcher.find()) {
            return matcher.group();  // 返回找到的第一个匹配项
        } else {
            return "";
        }
    }


}
